MySQL5.7 JSON新特性
项目实践
背景
在一次项目中，有一个这样的需求，就是可以编辑一个报名表单结构，同时能够保存用户的报名表单数据，并且根据条件进行搜索。由于表单结构可变，并且搜索项根据表单结构查询，因此这块使用了MySQL5.7新特性json函数。因为一开始考虑到这个问题，原想着用MonoDB去实现，但觉得觉得在项目初步阶段，为了这样一个小需求，使用一个NoSQL数据库，仅保存这一点东西，不太合理，恰巧有伙伴了解到MySQL在5.7版本支持了json的存储，因此把数据库升级到MySQL5.7.x使用JSON这个新特性。不得不说MySQL大有一统数据库的野心，一个关系型数据库，居然支持json形式存储。
配置
项目使用SpringBoot，SpringDataJpa。由于SpringDataJpa里面对MySQL5.7的json支持不是很够，因此需要手写sql语句。由于初期并没有考虑MySQL优化这方面，只想着把服务给跑起来，后续再进行优化。因此手写的sql语句并没有顾及到全表扫描等问题。
代码
由上图可以看到，sql语句并没有对优化做出任何努力。这在后续会进行一定的优化。主要目的是为了了解json的函数。图中使用的json函数包括JSON_CONTAINS_PATH，以及查询json字段里面的key，value。sql语句的意思是，如果表单里有某个字段，就查这个字段的值，如果表单里没有这个字段，就跳过去，不查。
JSON函数
概述
从5.7.8开始，MySQL提供了一个原生的json类型，因此json不再以字符串形式存储，而是采用一种允许快速读取文本元素的内部二进制格式。MySQL5.7.8还提供了一些基于json的函数，包括：
- 创建JSON：
- JSON_ARRAY()，
- JSON_MERGE()，
- JSON_OBJECT()
- 查询JSON：
- JSON_CONTAINS()，
- JSON_CONTAINS_PATH()，
- JSON_EXTRACT()，
- JSON_KEYS()，
- JSON_SEARCH()
- 修改JSON：
- JSON_APPEND()，
- JSON_ARRAY_APPEND()，
- JSON_ARRAY_INSERT(),
- JSON_INSERT(),
- JSON_QUOTE()，
- JSON_REMOVE()，
- JSON_REPLACE(),
- JSON_SET(),
- JSON_UNQUOTE()
- 获取JSON的META信息：
- JSON_DEPTH(),
- JSON_LENGTH()
- JSON_TYPE()
- JSON_VALID()
在5.7.9之后可以使用column -> path作为JSON_EXTRACT(column, path)的快捷方式，并且可以出现在任何位置。-> 左边的参数是json数据的列名，右边的参数是json数据中的路径表达式，如$.name这样~
JSON类型
MySQL对JSON类型做了一些限制：
- JSON文本最大长度取决于系统常量：max_allowed_packet。
- JSON列没有默认值
- 与其他二进制类型列一样无法创建索引
函数
由于这篇文章写得足够详细MySQL对json的支持，因此不再重复造轮子，只写下在项目用到的几个函数。
JSON_CONTAINS_PATH()
- 语法：
JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path]…) - 说明：
- 结果会返回0或者1，表示指定的json_doc下某个路径或某些路径下是否包含待定的值
- 某些参数为NULL返回NULL
- 以下情况将报错：
- json_doc为不合法的json文本
- path参数中包含不合法的路径
- one_or_all参数为非’one’或者’all’的值
- 如果目标文本中没有指定的路径，则返回0，否则返回值依赖于one_or_all值
- ‘one’：表示json文本（json_doc）中只要存在至少一个指定路径（path）则返回1，否则返回0
- ‘all’：表示json文本(json_doc)中必须包含有所有指定的路径才返回1，否则返回0
- 项目中运用：
- 在项目中由于表单是可以随时变化的，而搜索项是不会改变，所以必须得判断该搜索项对应一个表单里面的路径是否存在，如果存在再去找对应的字段；如果不存在，则不会去寻找，跳到下一个条件。如上图。
- 语法：
JSON_EXTRACT()
- 语法：
JSON_EXTRACT(json_doc, path[, path]…) - 说明：
- 返回json_doc中与path参数
- MySQL5.7.9以后的版本支持’->’操作符作为本函数两个参数的便捷写法。
- 语法：